import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.rbac.dao.BaseDao;
import org.rbac.po.PermissionGroups;
import org.rbac.po.PermissionUsers;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;

import com.kingschan.jdbc.JdbcTemplete;
import com.kingschan.lang.StringUtil;
import com.kingschan.lang.date.TimeStampUtil;

public class GroupDataImport {
    public static Connection getConnection() throws Exception {
        try {
            Class.forName( "oracle.jdbc.OracleDriver" );//"com.microsoft.sqlserver.jdbc.SQLServerDriver"
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String userName = "htcloud";
        String userPass = "htcloud";
        String driverName ="jdbc:oracle:thin:@192.168.15.55:1521:htcloudorcl";
                //"jdbc:sqlserver://33.33.33.1:1433;DatabaseName=ExMiddleDB;useLOBs=false";
        Connection cn = null;
        try {
            cn = DriverManager.getConnection(driverName, userName, userPass );
        } catch (SQLException e) {
            e.printStackTrace();
        } 
        return cn;
    }
    
    public static void importGroup() throws Exception{
        ClassPathXmlApplicationContext cpa = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
        PlatformTransactionManager txManager=(PlatformTransactionManager) cpa.getBean("transactionManager");
        TransactionDefinition txDefinition =(TransactionDefinition) cpa.getBean("txDefinition");
        TransactionStatus status = txManager.getTransaction(txDefinition);
        BaseDao rbacdao=(BaseDao) cpa.getBean("rbacBaseDao");
       /* List<Object[]> lis= rbacdao.query("select * from permission_syslog limit 20");
        txManager.commit(status);
        System.out.println(lis.size());*/
        
        
        Connection con =GroupDataImport.getConnection();
        try {
            String sql="SELECT ID,CDEPTCODE,CDEPTNAME,NPARENT FROM CLOUD_FILE_ORGANIZATION";
            List<Map<String, Object>> lis=JdbcTemplete.queryForListMap(con, sql);
            
            
            PermissionGroups pg=null;
            for (int i=0;i<lis.size();i++) {
                Map<String, Object> map =lis.get(i);
                pg=new PermissionGroups();
                pg.setId(Integer.valueOf(map.get("ID").toString()));
                pg.setGName(map.get("CDEPTNAME").toString());
                pg.setGDeptno(map.get("CDEPTCODE").toString());
                pg.setGParent(Integer.valueOf(map.get("NPARENT").toString()));
                rbacdao.save(pg);
                if (i%100==0) {
                    System.out.println(i/100+"批提交成功。。。");
                    rbacdao.getSession().flush();
                    rbacdao.getSession().clear();
                }
            }
            txManager.commit(status);
            System.out.println("commit ...");
        } catch (Exception e) {
            e.printStackTrace();
            txManager.rollback(status);
        }finally{
            con.close();
        }
    }
    
    
    
    public static void importUser() throws Exception{
        ClassPathXmlApplicationContext cpa = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
        PlatformTransactionManager txManager=(PlatformTransactionManager) cpa.getBean("transactionManager");
        TransactionDefinition txDefinition =(TransactionDefinition) cpa.getBean("txDefinition");
        TransactionStatus status = txManager.getTransaction(txDefinition);
        BaseDao rbacdao=(BaseDao) cpa.getBean("rbacBaseDao");
       /* List<Object[]> lis= rbacdao.query("select * from permission_syslog limit 20");
        txManager.commit(status);
        System.out.println(lis.size());*/
        
        
        Connection con =GroupDataImport.getConnection();
        Map<String, Object> map=null;
        try {
            String sql="SELECT MAX(A.PID) PID, MAX(A. NAME) NAME, MAX(A.LOGIN_NAME) LOGIN_NAME, MAX(A. PASSWORD) PASSWORD, MAX(A.SEX) SEX, MAX(a.EMAIL) EMAIL, MAX(a.MOBILE_PHONE) MOBILE_PHONE, MAX(a.REMARK) REMARK FROM t_user_info a WHERE a.email IS NOT NULL AND a.STATE = 1 GROUP BY email HAVING count(*) = 1";
            List<Map<String, Object>> lis=JdbcTemplete.queryForListMap(con, sql);
            
            
            PermissionGroups pg=new PermissionGroups();
            pg.setId(1);
            PermissionUsers pu =null;
            for (int i=0;i<lis.size();i++) {
                map=lis.get(i);
                pu = new PermissionUsers();
                pu.setUAccount(StringUtil.null2Empty(map.get("LOGIN_NAME")));
                pu.setUAlias(StringUtil.null2Empty(map.get("NAME")));
                pu.setUCdate(TimeStampUtil.getCurrentDate());
                pu.setUGroup(1);
                String mail=StringUtil.null2Empty(map.get("EMAIL"));
                if (null!=mail&&!mail.isEmpty()) {
                    System.out.println(mail);
                    pu.setUMail(mail);
                }
                String phone=StringUtil.null2Empty(map.get("MOBILE_PHONE"));
                if (phone.matches("[0-9]{11}")) {
                    System.out.println(phone);
                    pu.setUMobile(phone);
                }
                pu.setUPwd(StringUtil.null2Empty(map.get("PASSWORD")));
                pu.setUStatus(Short.valueOf("1"));
                String sex =StringUtil.null2Empty(map.get("SEX"));
                if (sex.equals("男")||sex.equals("1")) {
                    pu.setUSex(true);
                }else{
                    pu.setUSex(false);
                }
                pu.setURemarks(StringUtil.null2Empty(map.get("REMARK")));
                System.out.println(map);
                rbacdao.save(pu);
                
                if (i%100==0) {
                    System.out.println(i/100+"批提交成功。。。");
                    rbacdao.getSession().flush();
                    rbacdao.getSession().clear();
                }
            }
            txManager.commit(status);
            System.out.println("commit ...");
        } catch (Exception e) {
            System.out.println(map);
            e.printStackTrace();
            txManager.rollback(status);
        }finally{
            con.close();
        }
    }
    public static void main(String[] args) throws Exception {
        
    }
}
